Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 003 / db3notes.arc / DB3N8510.TXT < prev next >

Wrap

Text File | 1985-10-25 | 48.7 KB | 1,328 lines

dBASE III Anomalies and Workarounds 1.1 How to use this Section 1.2 @...GET alias to @...SAY...PICTURE >>> @...GET alias in Format File Any command that allows editing of a record will only operate in the SELECTed work area. However, attempting an @...SAY...GET using an ALIAS or SELECT to access another work area in a format (.FMT) file will not produce an error message. Instead, the entire line is suppressed, including the SAY statement. The Developer's Release of dBASE III will return the "Variable not found" error message if any attempt is made to GET a field from another work area. In versions 1.0 and 1.1 of dBASE III, the command: @ 10,4 SAY "Testing " GET B->Test in a format file will not display anything on the screen. Issuing this command from the dot prompt or in a command file will return "Variable not found." An alias name is acceptable in a SAY statement, even in a format file. @ 10,4 SAY "Testing " + B->Test will display properly. Notice that in the first example neither the SAY nor the GET pertaining to the second work area are displayed. [1.0, 1.1, D.R.] >>> @...SAY...PICTURE "@(" Displaying negative numeric memory variables with the @...SAY command and the PICTURE function "@(" to enclose negative numbers in parentheses, will not display the PICTURE correctly. Blanks will display between the beginning parenthesis and the memory variable contents. For example: number = 23.15 @ 10,10 SAY number PICTURE "@(" ( 23.15) The workaround is as follows: STORE STR( number, 12, 2 ) TO mem1 ^------------ Use an appropriate length and decimal value. STORE AT( "-", mem1 ) TO mpos @ 10,10 SAY SPACE(mpos-1) + "(" + SUBSTR(mem1,mpos+1) + ")" (23.15) [1.0, 1.1, D.R.] >>> @...SAY...PICTURE "$", ",", and "9" Using the template symbols "$", ",", and "9" in an @...SAY PICTURE clause may cause more than one dollar sign to be displayed on the screen. Specifically, if the value being displayed does not fill positions in the display string preceding the comma position, a dollar sign will be displayed in place of the comma. For example: mem = 123456 @ 10,0 SAY mem PICTURE "$999,999,999" @ 11,0 SAY mem PICTURE "$999,999,999,999" will output: $ $123,456 $ $ $123,456 The PICTURE template, ($), is intended to replace leading zeros in a numeric display with dollar signs. This means that dollar signs should always display in a fixed position format. To display a fixed position dollar sign leading a numeric expression with embedded commas, use two @...SAY commands, one to display the dollar sign and the other to display the numeric variable with the embedded commas. For example, mem = 1234.56 @ 10,10 SAY "$" @ ROW(),COL() + 1 SAY mem PICTURE "99,999.99" If you wish a leading dollar sign that is floating for numeric variables with embedded commas, a feature not directly supported by dBASE III, use the following command file to format the numeric variables. * Commas.PRG PARAMETERS mem mvar = SUBSTR( STR( mem, 9, 2 ), 1, 3 ) + ","; + SUBSTR( STR( mem, 9, 2 ), 4, 3 ); + SUBSTR( STR( mem, 9, 2 ), 7, 3 ) cntr = 1 DO WHILE SUBSTR( mvar, cntr, 1 ) $ ", " cntr = cntr + 1 ENDDO cnum = SPACE( cntr - 1 ) + "$" + SUBSTR( mvar, cntr ) @ 10,0 SAY cnum * EOP Commas.PRG STORE 1234.56 TO x DO Commas WITH x outputs: $1,234.56 [1.0, 1.1, D.R.] This formula will work with numbers as large as $999,999.99. Larger numbers require that the length argument of the STR() function and the length and starting point arguments of the SUBSTR() function be changed to accommodate the larger number. For numbers as large as $999,999,999.99 the second line of the preceding program should be changed to: mvar = SUBSTR( STR( mem, 12, 2 ), 1, 3 ) + ","; + SUBSTR( STR( mem, 12, 2 ), 4, 3 ) + "," ; + SUBSTR( STR( mem, 12, 2 ), 7, 3 ) +; SUBSTR( STR( mem, 12, 2 ), 10, 2 ) This problem persists in the Developer's Release including both the PICTURE clause and the TRANSFORM() function. The work-arounds are a little bit easier given some new functions including TRANSFORM() itself. To display a numeric expression with a leading dollar sign in fixed position and embedded commas concatenate a dollar sign to the result of the TRANSFORM() of the numeric expression. For example, mem = 1234.56 @ 10,10 SAY "$" + TRANSFORM( mem, "99,999.99" ) To display a floating dollar sign leading a numeric expression with embedded commas, insert the dollar sign into the result of the TRANSFORM() function on the numeric expression. For example, mem = 1234.56 @ 10,10 SAY SPACE(10-LEN(LTRIM(TRANSFORM(mem,"99,999.99")))); ^--------- Length of the TRANSFORM() picture. + "$" + TRANSFORM(mem,"99,999.99") It is important to note that the first element in the SPACE() function argument must be the length of the TRANSFORM() picture. If, for example, the picture is "999", then the element is three. >>> @...SAY...PICTURE "@X" and "@C" The C and X functions documented in the manual reference to the @...SAY command will always display positive numbers as credits (CR) and negative numbers as debits (DB). Use the following program segment if you need them to display in the reverse order (that is, positive numbers as debits (DB) and negative numbers as credits (CR)): DO CASE CASE number > 0 @ row,col SAY STR(number,17,2) + "DB" CASE number < 0 @ row,col SAY STR(-number,17,2) + "CR" OTHERWISE @ row,col SAY STR(number,17,2) ENDCASE [1.0, 1.1, D.R.] >>> @...SAY...PICTURE "@A" and "@!" The A and ! PICTURE clause functions are mutually exclusive. That is, you cannot combine the two to make a new function that will limit data input to alphabetic characters and force the letters to uppercase. If A and ! are used together, only the second function will be in effect. For example: PICTURE "@A!" is equivalent to PICTURE "@!" PICTURE "@!A" is equivalent to PICTURE "@A" [1.0, 1.1, D.R.] >>> @...SAY...PICTURE Logical Variable There is no PICTURE clause that applies to a logical variable in an @...GET command. However, if you attempt to use a PICTURE clause on a logical field or memory variable, dBASE III will not trap it as an error. When the READ is executed, dBASE III will not pause for user input, and the value of the field or variable will not be changed. One possible occurrence of this is when a memory variable is declared PUBLIC but is not initialized before an @...GET command. (dBASE III will automatically initialize a PUBLIC memory variable to a logical .F.) If the variable was not intended to be a logical variable, the above symptoms will manifest themselves. You may want to use the SPACE() function to initialize character variables and zero to initialize numeric variables. [1.0, 1.1, D.R.] >>> @...SAY...PICTURE "@Z" The zero-suppress function, @Z, will not suppress the decimal point when it is used with a non-integer value of zero. To work around this display problem, use the following command sequence instead of the @Z function. IF STR( number, 5, 2 ) <> " 0.00" @ 10,0 SAY number ENDIF [1.0, 1.1] 1.3 APPEND to CONFIG.DB >>> CONFIG.DB with SCOREBOARD The entry SCOREBOARD=OFF has no effect if set in CONFIG.DB. SETing the SCOREBOARD OFF suppresses the display of status and error messages to line 0 . The SCOREBOARD setting can only be changed from a command file or the dot prompt with the command SET SCOREBOARD OFF. 1.4 COPY [STRUCTURE] to DO WHILE 1.5 EDIT to LABEL limitations >>> EDIT If BROWSE is used on an indexed file and is terminated with a Ctrl-Q or Ctrl-W and followed with an EDIT command, keys which advance or regress through the database file (PgUp, PgDn, uparrow, downarrow, Ctrl-E, Ctrl-R, Ctrl-C) will not work properly. Sometimes these keys will drop the user out of EDIT to the dot prompt. Other times they will move two or three records instead of one. The display will sometimes lock on the current record. These keys may also cause the pointer to be positioned at the next to last record in the index. [1.0, 1.1, D.R.] >>> INKEY() The INKEY() function of the Developer's Release does not always read the leftarrow key as CHR(19). Running the following program will demonstrate that dBASE III will trap this key only rarely. i = 0 DO WHILE i <> 13 i = INKEY() ? i ENDDO When you run this program, you will notice that dBASE III treats the leftarrow key as a Ctrl-S, and will pause or start scrolling accordingly. Sometimes the key will be trapped and INKEY() will return 19; however, this is far less common than the former result. [D.R.] 1.6 MODIFY STRUCTURE to PCOL() and SET MARGIN TO 1.7 RECNO() to ROUND() 1.8 SET <full-screen> to SORT >>> SET RELATION TO The command syntax SET RELATION INTO <file> TO <key> will not SET the RELATION although no error message is displayed and DISPLAY STATUS shows the RELATION as SET. The SET RELATION command is sensitive to the order that the INTO and the TO clauses are specified. Apparently, everything after the INTO clause is ignored. Be sure to check that you have specified the TO clause before the INTO clause if your RELATIONs appear to fail. [1.0, 1.1] >>> SORT (1) Attempting to SORT a file with SET FILTER TO SUBSTR(fieldname,n,n) = <value> will return a "*** STACK OVERFLOW ***" and drop you out of dBASE III to the operating system. (2) SORTing a database file from a command file that is nested 13 levels deep will fail with a system crash. The SORT command will return a status message stating that a percentage of the records in the original file were SORTed, but then the computer will freeze with no error message or warning. (3) Attempting to use the command SORT TO <Filename> ON <Fieldname> with a RELATION SET produces the error message "*** STACK OVERFLOW ***" and will drop you out of dBASE III to the operating system prompt. This will not occur if the RELATION is SET to RECNO(). (4) One cannot SORT to a file in a directory other than the current one. An attempt to do this will not produce an error message and will not create the designated file. Instead, a file name W44 will be created in the current directory of the default drive. (5) The effective limit of SORT is in the 32,000 record range. SORTing a file with more than 32,000 records will produce the following error messages: nn% Sorted Records do not balance...(PROGRAM ERROR) 100% Sorted The SORTed file will not contain all the records from the original. The workaround for sorting databases in excess of 32,000 records is to INDEX and then COPY. The procedure is as follows: USE <filename> INDEX ON <sort key> to Temp COPY TO Sorted [1.0, 1.1] >>> SORT with 66 records SORTing a file that has more than 66 records in such a way that the resulting file has exactly 64 records will produce a file in which the first record is corrupted. [1.0, 1.1] 1.9 STORE to ZAP >>> USE An attempt to use the PC/MS-DOS directory navigation path (..) in a USE command will give unexpected results. USEing the database will make the file active, but there will be no ALIAS. An attempt to USE another database file in another SELECT area using the same technique, will cause the error message "ALIAS name already in use" to be displayed. * ---Open first database file. USE ..\nme\Budget DISPLAY STATUS Currently selected database: Select area - 1, Database in use: C:..\nme\Budget.dbf Alias - * ---Open another database file. SELECT 2 USE ..\nme\Sumfrm ALIAS name already in use ? USE ..\nme\sumfrm If an ALIAS is assigned in both USE commands, no error results. If you need to avoid specifying the full path name, use the RUN command to log the previous directory and then specify the datafile to USE. For example, RUN CD .. USE nme\Budget SELECT 2 USE nme\Sumfrm [1.0, 1.1, D.R] 1 dBASE III Programming 1.1 Program documentation 1.2 Database file structure 1.3 Debugging - program break points 1.4 dFORMAT 1.5 MEMO fields 1.6 Setting the system date and time 1.7 Recreating a corrupted dBASE III header 1.8 Simulating the JOIN command 1.9 Limitations 1.10 Capatilizing 1.11 Left-justifying Character Fields 2 dBASE III Frequently Asked Questions 2.1 Installation 2.2 Commands 2.3 New data types 2.4 Memory variables 2.5 Printing 2.6 Data transfer 3 dBASE III Reference 3.1 @...GET to Boolean Operators >>> @...GET PICTURE "@B" The function "@B" is used with the @...SAY...GET statement to left-justify variables of numeric type. It is most useful when SAYing a numeric field or memory variable that is more easily understood as a character string, such as a part number. Use of this FUNCTION with GET, however, causes a slight change in the way numeric variables are read from the screen that may cause some difficulties. A numeric memory variable will default to a length of ten digits when initialized; however, if you are using the PICTURE function "@B" in an @...GET statement, a numeric memory variable will GET the width of the memory variable exactly as initialized, even if a template symbol is used. Initializing a memory variable to zero will cause the GET statement to display one zero on the screen. A READ command will allow one digit only to be entered to the memory variable. This occurs whether the memory variable is initialized to 0 or 0000. For example: SET DELIMITERS ON w = 1234 x = 0 y = 0000 @ 9,0 GET w PICTURE "@B9999" @ 10,0 GET x PICTURE "@B9999" @ 11,0 GET y PICTURE "@B9999" will produce: :1234: :0: :0: A READ command will allow four characters to be entered in the first variable, but only one character in the next two variables. If the "@B" function is used, initialize the memory variable to the proper length or the input may be truncated. 3.2 CHR() to FILE() function >>> Demonstration Disk (Runtime+) In the Developer's Release of dBASE III, the dBRUN programs from the Demonstration Disk are not compatible with the full system. Code crunched with DBC.COM from the Demonstration Disk can only be run with dBRUN from the Demonstration Disk. Code crunched with DBC.COM from the Developer's Disk can only be run with the full dBASE III system or the dBRUN disk, purchased separately. The error message: No database is in USE. Enter filename: is a common indicator that the incorrect dBRUN or dBC is being used. 3.3 FIND to MODIFY STRUCTURE 3.4 Numeric fields to PARAMETERS 3.5 PRIVATE to PROW() >>> PROW() When issuing SET PRINT ON, PROW() will be set to 2, regardless of its previous setting. An EJECT will reset PROW() to 2, not 0. Issuing an EJECT with SET PRINT OFF will reset PROW() to 0. When SET PRINT is ON, PROW() may never equal 0 or 1. Any attempt to test for PROW() = 0 or PROW() = 1 will work only when SET PRINT is OFF. 3.6 PUBLIC to REPORT FORM 3.7 RELEASE to SET PROCEDURE >>> SET COLOR TO ,<cr> The command: SET COLOR TO ,<cr> will produce black on black, even if there is no space after the comma. 3.8 SET RELATION to warnings 3.9 dBase III File Structure 3.10 dBASE III Memo File Structure 3.11 Installation and Configuration 4 dBASE III Sample Programs 4.1 Left Justifying Character Fields by Stanley Ballenger Converting a numeric field to a character field using MODIFY STRUCTURE will leave the character field right-justified. If you find that you need the new character field to be left-justified, the following procedure will be a welcome addition to your procedure library. This procedure takes any database file and left-justifies all of the character fields in each record, trimming all the leading blanks. It uses an EXTENDED STRUCTURE to hold the names of all the character fields. Then, taking each character field in turn, it passes through the entire database file left-justifying that character field's values. The next character field is read from the EXTENDED STRUCTURE and the process is repeated. This proceeds until there are no more character fields to left-justify. Note that this procedure makes as many passes through your database file as there are character fields and, as such executes quite slowly. * Program....: Ljustify.PRG * Author.....: Stanley Ballenger * Date. .....: July 1, 1985 * Notes......: Left-justifies all character type fields in a * database file. * PRIVATE start, end, string, fname * ---Open files. SELECT 1 USE Yourfile COPY STRUCTURE EXTENDED TO Temp SELECT 2 USE Temp SET FILTER TO Field_type = "C" GO TOP CLEAR * ---Set up field count and display it. fldcount = 0 column = COL() + 10 @ ROW(),column SAY STR( fldcount,10 ) + " Field values replaced" * ---Justify character fields. DO WHILE .NOT. EOF() fname = Field_name SELECT Yourfile GO TOP end = LEN( &fname ) * ---Remove leading blanks for the current field. DO WHILE .NOT. EOF() start = 1 string = &fname * ---Remove leading blanks from cu~rrrent field. DO WHILE SUBSTR( string,start,1 ) = " " .AND. start < end start = start + 1 ENDDO * ---Replace field if it is left-justified. IF start <> 1 REPLACE &fname WITH SUBSTR( string,start,end ) fldcount = fldcount + 1 @ ROW(),c~orlumn SAY STR( fldcount,10 ) ENDIF * ---Get next record. SKIP ENDDO SELECT Temp * ---Get next character field name. SKIP ENDDO * ---Clean up. CLOSE DATABASES ERASE Temp.DBF RETURN * EOF Ljustify.PRG 4.2 Creative Uses of Database Files for Reporting Database files are central to dBASE II and dBASE III. They are the repositories of the information we want to record and access. Typical uses of database files are mailing lists, customer lists, records of purchase requisitions, or accounts receivable and payable. These will always be the most important uses for database files. However, there are other ways to use database files that may seem a bit more exotic at first, but they can be very powerful. The following program shows how a database file can be used to supply a program with variable parameters. In this case, the database file Conditn.DBF holds the number of different conditions on which the program is REPORTing, sparing us the necessity of hard-coding the conditions into the program itself. If later we want to modify or add conditions, we need only modify Conditn.DBF. This program performs the following function. It reports from the log of all the support calls that I have received in the last two weeks, breaking them down by product (dBASE II, dBASE III, Framework, Friday, and other utility programs). It uses REPORT FORM for its ease, but also does some statistical reporting that the REPORT FORM cannot perform. Since my database file has no numeric fields, I have no need for totals and subtotals. However, I do want to know the count of calls for each product. The program uses EJECT, NOEJECT, PROW(), and PCOL() to position the printer correctly. The structure for the database file Conditn.DBF is as follows: Structure for database file: Conditn.DBF Field Field Name Type Width Dec 1 Condition Character 15 ** Total ** 16 It has one record for each product: Product = 'D3' Product = 'D2' Product = 'FW' Product = 'FR' Product = 'UT' for a total of five records. * Program ..: Cumprint.PRG * Author ...: Ralph Davis * Date .....: October 1,1985 * Note .....: Prints counts from cumulative call log. SET TALK OFF ACCEPT " FILENAME: " TO filename ACCEPT "FORM NAME: " TO formname USE &filename SELECT 2 USE Conditn * ---EJECT page to reset line and column counters. SET PRINT ON EJECT * ---Initialize control variables. printmore = .T. memcond = Condition DO WHILE printmore SELECT &filename COUNT FOR &memcond TO mcount IF mcount > 0 REPORT FORM &formname NOEJECT TO PRINT FOR &memcond SET DEVICE TO PRINT * ---Skip to next page if line counter is past 58. IF PROW() > 58 @ 5,0 ENDIF @ PROW()+2,1 SAY "NUMBER OF CALLS: " @ PROW(),PCOL()+1 SAY mcount SET DEVICE TO SCREEN EJECT ENDIF * ---Return to control file. SELECT Conditn * ---Get next condition. SKIP * ---Leave program if no more conditions. IF EOF() EXIT ELSE memcond = Condition ENDIF ENDDO SET PRINT OFF SET TALK ON RETURN * EOP Cumprint.PRG 5 dBASE III Technical Notes 5.1 Simple List Program dBASE III offers standard list reporting through the REPORT FORM command. The REPORT FORM is easy to learn and very useful for column formatting whether you use one file or several files that are linked with the SET RELATION command. Often, however, you may find the REPORT FORM restrictive and so find it necessary to design a custom report. There are two fundamental reasons for this. First, there are physical limitations to the REPORT FORM definition. Specifically, there are limits to the number of fields and characters in a report definition. If you have a complex or large report, you may have run into these limits. Second, your report requires features that are not supported by the REPORT FORM. These may include statistical calculations such as averaging, multiple parent-child relations, formatted numeric fields, and non-columnar format, such as a pre-printed form.In all of these cases a custom report must be written. The purpose of this article is to help you understand the concepts behind writing a custom report by way of a working example. The following sample program illustrates a method to report on two files with one to many relationships. The program was originally written for a school that employs sales people to recruit students. It generates a report that details all the students recruited for each recruiter and the current session. There are two files used. One is Recruit.DBF and the other is Referral.DBF. The two files contain a common field, Ref_code, a unique code assigned to each recuiter. In the Referral.DBF file, Ref_code indicates which recuiter signed up that particular referral. For each record in the Recruit file there may be several associated records in the Referral file. The Recruit database file contains information regarding the sales people: name, address, city, state, zip, and the unique referral code (Ref_code). The Referral database file contains student and class information: name, school location, class level, and referral code (Ref_code), and is INDEXed ON Ref_code TO Code.NDX. As with any program, the first part sets up a specific environment. SET defaults are changed, the screen is CLEARed, the necessary files are opened, and the printer is accessed. All the memory variables utilized globally by the program are then initialized. A page counter is set up (m_pagectr) and a line counter is established (m_line). The line counter, m_line, is initialized to a large number so that the first page is EJECTed. Next, a loop is established to instruct dBASE III to process the commands until there are no more records in Recruit.DBF. This is indicated when the EOF() function returns a true value. The idea is to use Recruit.DBF as the point of reference into the Referral.DBF. The next step is to find and print all the records from the Referral.DBF file, that have the same Ref_code as the current record in Recruit.DBF. This is done by SELECTing the second file and SEEKing Recruit->Ref_code. Note the use of the alias name when SEEKing with a field form a non-SELECTed work area. A test must then be made to determine if there are any referrals for the current recruiter. The statement IF EOF() tests for this. If there are no referrals found by the SEEK command, EOF() returns a true (.T.). In this example, if a record was not found, dBASE III is instructed to SELECT Recruit.DBF, SKIP to the next record, LOOP around to the DO WHILE .NOT. EOF() statement, and start the process again. If a record is found, then the record pointer is positioned at the first occurrence of Ref_code in Referral.DBF. In order to display all the records that have the same referral code, it is necessary to enter a second DO WHILE loop that will skip through Referral.DBF until the Ref_code in this file no longer matches Recruit->Ref_code, or the end of the file is reached. The statement "IF m_line > 56" tests the line counter to determine whether or not to eject to a new page before printing. This is where the statement "m_line = 90" makes sense. Storing 90 to m_line every time a new record is selected from the Recruit file ensures that the first time the statement IF m_line > 56 is evaluated, headings will be printed since m_line is greater than 56. Notice that right after that IF statement m_line is reset to 11, the location where the first line of data will be printed. After the ENDIF, the first record from the Referral.DBF is printed, the line counter is incremented, and the record pointer SKIPs to the next record. It is necessary to increment the line counter so that the printer moves forward. When sending data to the printer for a custom report, @...SAYs should move from top to bottom and left to right. If an attempt is made to print to a row and column position that has already been printed on the current page, then a formfeed is sent to the printer. In our example, we use this concept to our advantage. The headings are set up to begin printing on line 1, which requires the printer to eject to the top of a new page if it isn't already positioned there. dBASE III stays in this loop until all the records from Referral.DBF that match Recruit->Ref_code are printed. Each time a new record is to be printed, m_line is tested, and ejects to a new page when appropriate. Once all the matched records are printed from Referral.DBF, the program SELECTs the Recruit.DBF file, moves the record pointer to the next record, and loops around to the first DO WHILE .NOT. EOF() to begin processing a new record. When all the records in Recruit.DBF have been reported on, an EJECT is sent to be sure that the last line of data is printed. The printer is then disabled (SET DEVICE TO SCREEN), the files are closed, and control is RETURNed to the calling program or the dot prompt. To test this program, add two or three records to Recruit.DBF with Ref_codes 101 and 102. Then add a few records to Referral.DBF and include the same Ref_codes, 101 and 102. Once this is done, INDEX Referral.DBF ON Ref_code TO Code.NDX. To execute the program, turn the printer on and type DO Report. Pseudocode 1. Set Up the Working Environment. Clear the screen. Define environmental SETtings. Define work areas. Open database files with appropriate indices. Define relations and filters. Select the initial work area. Initialize global variables. Set the page counter to 0. Set the line counter to a value greater than the page length. Direct output to the printer. 2. Print the Report. Process all the records in the primary work area. Select the secondary work area. Move to the first secondary record. If there aren't any. Select the primary work area. Move to the next primary record. Start the process over. Process all the records in the secondary work area that match the primary record. If the line counter is greater than the page length Advance the printer to a new page. Print page heading. Print page number and date. Print report title. Print field titles. Increment the page counter. Set the line count to the row position of the first detail line. Print one detail line. Increment the line count. Move to next secondary record. Select the primary area Move to next primary record. 3. Restore the Environment. Advance the printer to a new page. Direct output to the screen. Close all the work areas. Return to calling program or the dot prompt. Report The following command file was built from the pseudocode listed above. * Program..: Report.PRG * Author...: Karen A. Robinson * Date.....: October 1, 1985 * Notes....: USEs Referral.DBF and Recruit.DBF to generate a * custom report of student referrals by recruiter. * CLEAR SET TALK OFF * --- Open work areas. SELECT 2 USE Referral INDEX Code SELECT 1 USE Recruit SET DEVICE TO PRINT * --- Initialize memory variables for the page headings. STORE 1 TO m_pagctr STORE 90 TO m_line * --- Print all recruiters and their respective referrals. DO WHILE .NOT. EOF() * --- Calculate column positions so that the name and address * --- of the recruiter will be centered. STORE 40 - LEN(TRIM(Name))/2 TO m_1 STORE 40 - LEN(TRIM(Address))/2 TO m_2 STORE 40 - ((LEN(TRIM(City)) + LEN(State) + LEN(Zip) + 4)/2); TO m_3 * -- Find the first referral from Recruit.DBF. SELECT Referral SEEK Recruit->Ref_code * --- Test for the existence of a referral. * --- If one is not found, then get the next recruiter * --- and start the process over. IF EOF() SELECT Recruit SKIP LOOP ENDIF * --- The first referral has been found. The following * --- prints all the records from Referral.DBF that have * --- the same Ref_code as the Ref_code in Recruit.DBF. DO WHILE Ref_code = Recruit->Ref_code .AND. .NOT. EOF() * --- Test for a new page. If the line count exceeds * --- 56, a new page is required. IF m_line > 56 * --- Print page and column headings. @ 1, 28 SAY 'STUDENT REFERRAL REPORT' @ 1, 65 SAY 'Page ' + STR(m_pagctr,3) @ 2, 65 SAY 'Date ' @ 2, 72 SAY DATE() @ 4,m_1 SAY Recruit->Name @ 5,m_2 SAY Recruit->Address @ 6,m_3 SAY TRIM(Recruit->City) + ', ' +; Recruit->State + ' ' + Recruit->Zip @ 9,14 SAY 'Level' @ 9,23 SAY 'Center' @ 9,43 SAY 'Student Name' * --- Increment page and line counts. STORE 11 TO m_line STORE m_pagctr + 1 TO m_pagctr ENDIF * --- Print a referral, a record from the Referral * --- database file. @ m_line,14 SAY Level @ m_line,23 SAY Center @ m_line,43 SAY Name * --- Increment the line counter. STORE m_line + 1 TO m_line * --- Get the next Referral for the current * --- recruiter. SKIP ENDDO * --- Get the next recruiter. SELECT Recruit SKIP ENDDO * --- Restore the environment and return. EJECT SET DEVICE TO SCREEN CLOSE DATABASES RETURN * EOF Report.PRG Conclusion In this article, we have discussed custom reporting using Report.PRG as an example. To recapitulate, Report.PRG utilizies several reporting techniques. First, the program reports data from two database files that have a common field, Ref_code. Second, a page header is centered on the top of each page. Third, a line counter is incremented to a large number which causes the formatted report to print on a separate page for each record that meets a sepcified condition. You can change Report.PRG to fit your needs, replacing filenames and field names with those of your own. 5.2 Passing Parameters to Assembly-language Subroutines by Ralph Davis General Considerations With the introduction of the Developer's Release, dBASE III has acquired the ability to pass parameters to assembly language subroutines. In combination with the ability to LOAD and CALL the routines, you now have the possibility of much closer linking between a dBASE program and an assembler routine than was previously possible using the RUN command. The dBASE III Developer's Release Reference Manual documents this feature on pages 4-26A and 4-72A through 72C. It states that the address at which the variable is stored is passed in DS:BX; that you can pass any type of memory variable; that character variables terminate with the null character (ASCII zero); and that you must not shorten or lengthen the variables that you pass. In this article, I will explore some techniques for passing parameters to assembler routines from the Developer's Release of dBASE III, and make some suggestions for the most efficient ways to do this. How to Pass Variables One thing you will need to understand is the information dBASE III passes you. What form are the different variables in? What can you do with them? What are the best ways to do so? First of all, it is interesting to note that DS:BX points to the first byte of data, not the first byte of the variable. The first byte of all variables contains the length of the variable. You can obtain this easily by decrementing BX. For character variables, the length includes the null character string terminator at the end; therefore, the length you are concerned with is the length descriptor minus one. All other variable types--numeric, date, and logical--have a fixed length of eight bytes. Each one has a different format in memory. Secondly, although it appears that you can only pass one variable at a time, if you are methodical, you can pass as many variables as you want. As you might expect, variables are assigned to memory storage space sequentially. As long as they are not RELEASEd, and the memory space reassigned, they will be stored one right after the other. Therefore, by passing the first variable, you can easily locate all subsequent variables by using the length descriptors. Later in this article, I will go into greater detail about how to do this. Format of Variables in Memory Memory variables are stored in the following formats: 1. Character variables are stored with the length in the first byte, followed by the actual string, and ending with 00. 2. Numeric variables are stored in IEEE long-real format. They use eight bytes, which appear in reverse order (most significant byte at the highest memory address). The first bit is the sign bit: 0 for positive and 1 for negative. The next 11 bits are the exponent (that is, the power of 2) plus 1023 decimal (3FF hexadecimal). The final 52 bits are the mantissa, the part of the number following the decimal point. The actual value of the number is computed as 1.<mantissa> times 2 to the exponent-1023). For instance, 2 (1.000 times 2^1) is stored as: 00 00 00 00 00 00 00 40 Negative four, -4, (-1.000 times 2^2) is stored as: 00 00 00 00 00 00 10 C0 Notice that the bits of the mantissa represent negative powers of 2, from 2^(-1) (1/2) to 2^(-52) (approximately 1 divided by 4.5 quadrillion). 3. Logical variables are stored as eight bytes, apparently to reserve space for redefining them. All PUBLIC variables are initially defined as logical type with a value of false until they are given a value. The first byte is 01 for .T. and 00 for .F. This is the only byte you need be concerned with. 4. Date variables are also stored in reverse order MSB last) in encoded binary form. Suggestions Passing Multiple Variables When you pass a variable to an assembly language subroutine, you do so by issuing the command: CALL <routine> WITH <variable> For instance, if you store 'JOHN Q. PUBLIC' to mem1 and then CALL a routine with mem1, when your routine gains control, DS:BX will be pointing to the J in JOHN. Decrement BX to retrieve the length descriptor, in this case 15 (0F hex). To re-emphasize: the length includes the 00 which terminates all string variables. The actual string is only 14 characters long. If you initialize several memory variables at once, then CALL a routine with the first one, you can use the length descriptor to locate the next one. If, after storing 'JOHN Q. PUBLIC' to mem1, you store 'LOS ANGELES' to mem2 and 'CA' to mem3, you can pass mem1 to your routine, and locate mem2 and mem3 in the following manner. Decrement BX to get mem1's length. Add it to BX. Add one to BX for the terminating descriptor. Now BX is pointing to mem2's length descriptor. Add this value to BX and add one again; you are now pointing to mem3's length descriptor. To pass multiple variables in this fashion, it is critical that you initialize them one right after the other. For example: STORE 'JOHN Q. PUBLIC' TO mem1 STORE 'LOS ANGELES' TO mem2 STORE 'CA' TO mem3 Then you can CALL your routine and easily locate mem1, mem2, and mem3. If, however, you RELEASE and then redefine a variable, you will not be able to do this. The following sequence will not work: STORE 'JOHN Q. PUBLIC' TO mem1 STORE 'LOS ANGELES' TO mem2 ... <other commands not affecting memory variables> RELEASE mem1 STORE 'CA' TO mem1 I therefore suggest that when you pass variables to subroutines, STORE all of them immediately prior to invoking the routine. Even if the variables have been defined elsewhere, redefine them here with new names. Then replace the old variables upon returning from the routine. This will give you the greatest degree of control over the variables you pass to your routine. Passing Different Variable Types As I mentioned earlier, numeric variables are stored in IEEE format, and dates are stored in an encrypted binary form. Both of these formats are cumbersome to work with. So I recommend that you pass all date and numeric variables as characters. There will undoubtedly be applications where you will want the actual binary representation of numeric or date information-- ones using the 8087 or 80287, for example--and in these instances, of course, it is appropriate to pass the variable in its actual form. But for most purposes, it is much easier to pass the ASCII codes, then process them as needed in your routine. Since the only part of a logical variable that concerns us is the first byte, it is best to pass it as is. You then return 0 for false and 1 for true. The program Printchk.ASM, listed later in this issue, passes a logical variable, and determines program action based on the value returned by the assembler routine. Checking Printer Status by Ralph Davis Introduction We are all human, and need not be ashamed to admit that sometimes we try to print files when our printer is either turned off or not ready. If we do this from dBASE II or III, we may soon be facing the DOS prompt. Neither dBASE II nor III checks the printer before trying to print. The error recovery is left to DOS. If the printer is off-line, DOS issues the familiar "Write fault error writing device PRN Abort, Retry, Ignore" message. Turning the printer on-line and pressing "R" for "Retry" resumes the printing operation, and all goes well. Pressing "A" or "I," however, returns us to the operating system prompt, and may cause database file corruption. The following assembler routine, Prntchk.ASM, checks the printer status port for LPT1 and controls program flow according to its findings. It is written according to the specifications outlined in the September issue of TechNotes, with conditional assembler directives to permit the same source code to create programs either for dBASE II, dBASE III 1.0 and 1.1, or dBASE III Developer's Release. It functions somewhat differently in each environment. The program is called Prntchk rather than Printchk because of an anomaly in the Developer's Release with the LOAD command which obliges us to name LOAD modules with seven characters or less. Keep this in mind when naming programs for use with dBASE III. Discussion of Prntchk.ASM When you use Prntchk with dBASE II or pre-Developer's Release versions of dBASE III, you RUN it or QUIT TO it after assembling it as a .COM file. This version of the program checks the printer status and does one of three things: 1. Reports that the printer is available, and returns control to dBASE II or dBASE III. 2. Reports that the printer is turned off, advises you to turn it on, and retains control until it is turned on. 3. Reports that the printer is off-line and retains control until it is on-line. Notice that in cases 2 and 3, the program does not relinquish control. After issuing its report, it waits for you to adjust the printer and press any key to continue. It will not release control until it obtains a ready report from the printer. Thus it assures that dBASE will not attempt a printing operation until the printer is ready to carry it out, protecting your files from potential data loss. The program operates somewhat differently with the Developer's Release. Here, it initializes a logical variable to false (.F.), then passes this variable to Prntchk. Prntchk checks the printer status, and places either a 0 (.F.) or a 1 (.T.) back into the variable. Upon return, your dBASE program can test this variable and determine program flow based on it. Here is a short section of code from a printing program that is based on this idea. SET TALK OFF LOAD Prntchk ok2print = .F. DO WHILE .NOT. ok2print CALL Prntchk WITH ok2print IF .NOT. ok2print ? ? 'Printer is not ready - PLEASE CORRECT' WAIT ENDIF ENDDO The program cannot get past this DO WHILE loop until the printer is ready for output. .LFCOND PAGE 60,132 ; D3DR EQU 1 ; Assemble for dBASE III, ; Developer's Release. COM EQU 0 TRUE EQU 1 ; Define symbols FALSE EQU 0 ; for .T. and .F. ;************************************** CODESEG SEGMENT BYTE PUBLIC 'CODE' ASSUME CS:CODESEG,ES:CODESEG ;-------------------------------------- PRNTCHK PROC FAR IF COM ; ORG at 100H ORG 100H ; for .COM file. ENDIF START: JMP NEXT_STEP ; Skip past data. ; MESS1 DB 'PRINTER OFF-LINE - PLEASE ADJUST',0DH,0AH,'$' MESS2 DB 'PRINTER NOT TURNED ON - PLEASE TURN IT ON' DB 0DH,0AH,'$' MESS3 DB 'PRINTER AVAILABLE FOR PRINTING',0DH,0AH,'$' MESS4 DB 'PRESS ANY KEY TO CONTINUE',0DH,0AH,'$' OK DB ? ; NEXT_STEP: PUSH AX ; Save registers. PUSH BX PUSH DS PUSH ES PUSH CS POP ES MOV AX,40H ; Point to system data segment. MOV DS,AX MOV SI,8 ; Point to LPT1 port address. MOV DX,[SI] ; Load it into DX. INC DX ; Point to LPT1 status port IN AL,DX ; and read it. CMP AL,0DFH ; Printer OK? JNE OFF_LINE ; No, is it off-line? MOV ES:OK,TRUE ; Place .T. in temporary variable. IF COM MOV DX, OFFSET MESS3 ; Print report if .COM file. CALL PRINTMESS ENDIF JMP SHORT EXIT ; Return to dBASE. OFF_LINE: CMP AL,0CFH ; Is printer off-line? JNE TURNED_OFF ; No, it must be turned off. MOV ES:OK,FALSE ; Place .F. in temporary variable. IF COM MOV DX,OFFSET MESS1 ; Print report if .COM file. CALL PRINTMESS MOV DX,OFFSET MESS4 CALL PRINTMESS CALL CRLF ; Skip line. POP ES POP DS POP BX POP AX CALL WAIT ; Wait for keypress. JMP NEXT_STEP ; Go back and check status again. ENDIF JMP SHORT EXIT ; Leave if OK. TURNED_OFF: MOV ES:OK,FALSE ; Place .F. in temporary variable. IF COM MOV DX,OFFSET MESS2 ; Print report if .COM file. CALL PRINTMESS MOV DX,OFFSET MESS4 CALL PRINTMESS CALL CRLF ; Skip line. POP ES POP DS POP BX POP AX CALL WAIT ; Wait for keypress. JMP NEXT_STEP ; Go back and check status again. ENDIF EXIT: POP ES ; Restore registers. POP DS ; Now DS and BX are pointing POP BX ; to variable passed by dBASE III. IF D3DR MOV AL,ES:OK ; Get .T. or .F. from temporary ; variable. MOV BYTE PTR [BX],AL ; Place it in dBASE variable. ENDIF POP AX ; Restore remaining registers. IF COM INT 20H ; INT 20H if .COM file. ELSE RET ; Far return if Developer's Release. ENDIF ; PRNTCHK ENDP ;---------------------------------------- ; SUBROUTINES ;---------------------------------------- CRLF PROC NEAR ; Skips line. PUSH AX ; Save registers. PUSH DX MOV DL,0DH ; Print carriage return. MOV AH,2 INT 21H MOV DL,0AH ; Print line-feed. MOV AH,2 INT 21H POP DX ; Restore registers. POP AX RET ; Return to caller. CRLF ENDP ;---------------------------------------- PRINTMESS PROC NEAR ; Print message. PUSH AX ; Save registers. PUSH DS MOV AX,ES ; ES points to Prntchk's MOV DS,AX ; data. XOR AX,AX ; Zero AX. MOV AH,9 ; Call DOS print string function. INT 21H POP DS ; Restore registers. POP AX RET ; Return to caller. PRINTMESS ENDP ;---------------------------------------- WAIT PROC NEAR ; Waits for keypress. ; Does not check for Ctrl-Break. PUSH AX ; Save register. MOV AH,1 ; Call DOS, wait for keypres. INT 21H ; Don't check Ctrl-Break function. POP AX ; Restore register. RET ; Return to caller. WAIT ENDP ;---------------------------------------- CODESEG ENDS ;****************************************** END START 6 dBASE III Version 1.1 Change Summary